***
use "P:\ECB business areas\DGR\Staff\papouts\Research\Active\Firm Heterogeneities\Final datasets\instr_bank_level_dt.dta" , replace
 
ssc install distinct
ssc install unique
 
keep cntry_dbtr dbtr_id crdtr_id typ_instr_corr mb_flag

* number of banks per firm
by cntry_dbtr dbtr_id crdtr_id, sort: gen help = _n == 1 
by cntry_dbtr dbtr_id: gen nbanks = sum(help)
by cntry_dbtr dbtr_id: replace nbanks = nbanks[_N] 
drop help
* number of firms per country
egen tag = tag(cntry_dbtr dbtr_id) 
egen nfirms = total(tag), by(cntry_dbtr)
* number of firms per country that have multi or single relationships
gen multirel = 0
replace multirel = 1 if nbanks > 1 
egen tag2 = tag(cntry_dbtr dbtr_id multirel) 
egen nfirms2 = total(tag2), by(cntry_dbtr multirel)
* construct shares
gen helpshare = nfirms2 / nfirms
gen multirel_share = helpshare if multirel == 1 
by cntry_dbtr, sort: egen help = max(multirel_share)
replace multirel_share = help
drop help
gen singlerel_share = helpshare if multirel == 0 
by cntry_dbtr, sort: egen help = max(singlerel_share)
replace singlerel_share = help
drop help

drop tag tag2

*** 
* v2 only firms that have multiple relationships included in the instrument analysis
keep if multirel == 1 // if we want the previous version comment this out
 
* by firm, number of instruments
egen firmid = group(cntry_dbtr dbtr_id)
order firmid cntry_dbtr dbtr_id
sort firmid  typ_instr_corr

egen tag = tag(firmid typ_instr_corr) 
egen ninstr = total(tag), by(firmid)

* by firm, instrument - main bank
by firmid typ_instr_corr, sort: egen inst_mb = max(mb_flag)
by firmid typ_instr_corr, sort: egen inst_notmb = min(mb_flag)
tab inst_mb inst_notmb
gen group = 0 
replace group =1 if inst_mb == 1 & inst_notmb == 1 //  instrument only from the main bank
replace group =2 if inst_mb == 1 & inst_notmb == 0 //  instrument both from the main bank and other banks
replace group =3 if inst_mb == 0 & inst_notmb == 0 //  instrument only from other banks
egen tag2 = tag(firmid typ_instr_corr group) 
egen ninstr2 = total(tag2), by(firmid group)

* estimate the share and then take the average  for each group
gen instrshare = ninstr2 / ninstr
keep firmid cntry_dbtr dbtr_id nbanks multirel_share singlerel_share ninstr group ninstr2 instrshare
duplicates drop
reshape wide  ninstr2 instrshare , i(firmid cntry_dbtr dbtr_id nbanks multirel_share singlerel_share ninstr) j(group)

* estimate the average by country
replace instrshare1 = 0 if missing(instrshare1)
replace instrshare2 = 0 if missing(instrshare2)
replace instrshare3 = 0 if missing(instrshare3)

by cntry_dbtr, sort: egen share_onlymb = mean(instrshare1)
by cntry_dbtr, sort: egen share_bothmboth = mean(instrshare2)
by cntry_dbtr, sort: egen share_onlyoth = mean(instrshare3)

* estimate the average and then take the share for each country
replace ninstr = 0 if missing(ninstr)
replace ninstr21 = 0 if missing(ninstr21)
replace ninstr22 = 0 if missing(ninstr22)
replace ninstr23 = 0 if missing(ninstr23)

by cntry_dbtr, sort: egen instr = mean(ninstr)

by cntry_dbtr, sort: egen instr_onlymb = mean(ninstr21)
by cntry_dbtr, sort: egen instr_bothmboth = mean(ninstr22)
by cntry_dbtr, sort: egen instr_onlyoth = mean(ninstr23)

gen share_onlymb2 = instr_onlymb / instr
gen share_both2 = instr_bothmboth / instr
gen share_onlyoth2 = instr_onlyoth / instr


keep cntry_dbtr multirel_share singlerel_share share_onlymb share_bothmboth share_onlyoth instr instr_onlymb instr_bothmboth instr_onlyoth share_onlymb2 share_both2 share_onlyoth2

duplicates drop

label variable multirel_share "Share of firms with multiple relationships"
label variable singlerel_share "Share of firms with single relationship"
label variable share_onlymb "Share of instruments only from the main bank"
label variable share_bothmboth "Share of instruments both from the main bank and other banks"
label variable share_onlyoth "Share of instruments only from other banks"
label variable share_onlymb2 "Share of instruments only from the main bank"
label variable share_both2 "Share of instruments both from the main bank and other banks"
label variable share_onlyoth2 "Share of instruments only from other banks"


*export delimited "P:\ECB business areas\DGR\Staff\papouts\Research\Active\Firm Heterogeneities\Final charts\Revision\Share_Instr_stata.csv", replace 
*texsave using "P:\ECB business areas\DGR\Staff\papouts\Research\Active\Firm Heterogeneities\Final charts\Revision\Share_Instr_stata.tex", replace 
drop instr*
format *share share* %9.2f 

export delimited "P:\ECB business areas\DGR\Staff\papouts\Research\Active\Firm Heterogeneities\Final charts\Revision\Share_Instr_stata_v2.csv", replace  
texsave using "P:\ECB business areas\DGR\Staff\papouts\Research\Active\Firm Heterogeneities\Final charts\Revision\Share_Instr_stata_v2.tex", replace varlabel
	
keep cntry_dbtr multirel_share share_onlymb share_bothmboth share_onlyoth	
texsave using "P:\ECB business areas\DGR\Staff\papouts\Research\Active\Firm Heterogeneities\Final charts\Revision\Share_Instr_stata_v2_touse.tex", replace varlabel

